تعلم تحليل البيانات ببرنامج الإكسل بكل سهولة .. شرح بالصور

يعود استخدام جداول البيانات الإلكترونية إلى عام 1979 ولا يزال أحد أكثر الطرق شيوعًا لأجل مراجعة وتحليل البيانات ومعالجتها. اليوم ، لدى برنامج جداول بيانات الإكسل في كل مكان من ميكروسوفت أكثر من 750 مليون مستخدم وتستخدمه بعض أكبر الشركات في العالم.

أنا شخصياً أستخدم برنامج الإكسل كل يوم تقريبًا. هذا لأنه قد يمنحك نتائج أسرع من الأدوات المماثلة الأخرى.

يوفر الإكسل الساعات والأيام في تحليل البيانات وتقديمها بطريقة منظمة وقابلة للتعديل وسهلة الاستخدام عبر منصات ومواقع مختلفة ، وتطبيق الصيغ بسهولة ، كما يمكنك إنشاء ملفات عمل متنوعة.

7 ميزات تستعمل في تحليل البيانات بسهولة باستخدام الإكسل

أحد أسباب شهرة برنامج الإكسل هو أنه مليء بالميزات والوظائف التي يمكنك استخدامها لتنظيف بياناتك وتجميعها وتحويلها إلى محاور أو مخططات.

حيث توضح هذه المقالة سبع ميزات وقدرات لاستخدام تحليل البيانات في الإكسل . أعتقد أن كل مستخدم يجب أن يعرف عن ميزات تحليل البيانات هذه.

1- الجداول المحورية والمخططات

إحدى ميزاتي المفضلة في الإكسل هي المخططات المحورية.وهي ميزة أخرى اكتشفتها مؤخرًا هي الجداول المحورية.

تسمح لك المخططات المحورية بتصور البيانات الممثلة في الجداول المحورية واكتساب رؤى في لمحة. توفر الجداول المحورية طريقة سهلة لإعادة تنسيق الأعمدة والصفوف إلى مجموعات أو إحصائيات أو ملخصات.حيث يمكنك إنشاء مخطط على الجدول الخاص بك باستخدام ميزة PivotChart ضمن إدراج “Insert”.

لنفترض أننا نريد حساب السجلات المجمعة حسب الحالة الاجتماعية. تجعل الجداول المحورية هذا الأمر سهلاً للغاية عن طريق تحويل البيانات وتلخيصها.

لإنشاء PivotCharts و PivotTables “جدول محوري ومخطط محوري”، حدد أولاً نطاق البيانات الذي تريد تضمينه ، ثم انقر فوق إدراج> مخططات PivotCharts< PivotCharts و PivotTables. ينبثق إنشاء محرر PivotTable.

سيتم عرض النطاق المحدد في حقل الجدول / النطاق افتراضيًا. انقر فوق “موافق” لإنشاء الجدول المحوري.

ضمن حقول PivotChart ، اسحب Marital_Status من قائمة الحقول إلى مربع المحور (الفئة).

ثم اسحب Marital_Status من قائمة الحقول إلى مربع القيمة. بيانات Marital_Status هي سلسلة ، لذا يجب أن يكون تجميع القيمة افتراضيًا Count. إذا كان نوع البيانات رقميًا ، فسيكون الإعداد الافتراضي هو Sum.

يجب ملء الجداول المحورية والمخططات.كما يمكن إضافة أبعاد أو عوامل تصفية إضافية عن طريق سحب حقول جديدة إلى المربعات المقابلة.حيث يتم استخدامه على نطاق واسع لأنه يمكن جمع البيانات وتصورها في الإكسل ببضع نقرات فقط.

2- التنسيق الشرطي باستخدام الإكسل

تعال إلى التفكير في الأمر ، ربما أستخدم التنسيق الشرطي أكثر من أي ميزة أخرى في الإكسل . يسمح لك التنسيق الشرطي بتمييز الخلايا أو إخفائها بناءً على القواعد المحددة. قم بتطبيق القاعدة على خلية واحدة أو أكثر في نفس ورقة العمل. يساعد في إبراز القيم المتطرفة أو التكرارات أو الأنماط في بياناتك.

لنفترض أنك تريد إبراز جميع قيم سنة الميلاد في مجموعة البيانات الخاصة بك بعد عام 1987.

حدد العمود Year_Birth وانقر فوق تنسيق شرطي> تمييز قواعد الخلية> أكبر من . المحرر سوف يظهر.

أدخل 1987 وانقر فوق موافق. تتحول الخلايا الموجودة في الأعمدة ذات القيم الأكبر من 1987 إلى اللون الأحمر الفاتح.

إذا كنت تريد تعديل قاعدة التنسيق الشرطي التي أنشأتها للتو ، فاتبع المسار التنسيق الشرطي> إدارة قواعد التنسيق الشرطي.

يمكنك إنشاء قواعد جديدة أو تعديل القواعد الحالية من المدير.كما يمكن أن يكون لديك عدة قواعد تؤثر على جدول البيانات الخاص بك.

3- إزالة التكرارات في الإكسل

غالبًا ما تكون البيانات فوضوية ، لذا من المهم معرفة كيفية إزالة التكرارات. تتيح لك قواعد التنسيق الشرطي وضع علامة على التكرارات لمراجعتها قبل إزالتها. تتوفر ميزة إزالة التكرارات في البيانات> أدوات البيانات> إزالة التكرارات.

قم بتمييز مجموعة البيانات في الإكسل وانقر فوق إزالة التكرارات. يبدو محرر Remove Duplicates كما يلي:

يسمح لك المحرر بتحديد الأعمدة المراد تضمينها عند إزالة التكرارات. إذا كنت لا ترى أسماء الأعمدة ، فتأكد من تحديد خانة الاختيار تحتوي البيانات على رؤوس.

انقر فوق موافق. يتم تجاهل التكرارات من مجموعة البيانات. يخبرك بعدد القيم الفريدة المتبقية.

4- DAYS و NETWORKDAYS في الإكسل

DAY’S و NETWORKDAYS هما أدوات منفصلة ، لكنهما متشابهين بما يكفي لوجود إدخالات فردية في قائمتي.

تقوم الدالة DAYS ببساطة بإرجاع عدد الأيام بين تاريخين ، لكن وظيفة NETWORKDAYS مختلفة قليلاً من حيث إنها تستبعد عطلات نهاية الأسبوع والعطلات المحددة. يقوم فقط بإرجاع عدد أيام العمل بين تاريخين. كلا الصيغتين يسهل تذكرهما.

• تاريخ البدء : start_date
• تاريخ الانتهاء : end_date

= DAYS (start_date، end_date)

=NETWORKDAYS(start_date, end_date, [holidays])

أنا أقدر ميزة NETWORKDAYS حيث يتم استخدام الميزة في برامج المدارس ، لذلك يكون من المنطقي في بعض الأحيان استبعاد عطلات نهاية الأسبوع والعطلات من بيانات الاستخدام.

5- ميزة التصنيف في الإكسل

تطلب الدالة RANK أرقامًا وفقًا لحجمها بالنسبة إلى القيم الأخرى في القائمة وترجع أمرًا مرتبًا.

هذا يعني أنه إذا تم فرز القائمة بترتيب تصاعدي أو تنازلي ، فسيكون ترتيب الرقم هو موضعه.

على سبيل المثال ، قم بفرز الدخل بترتيب تنازلي بحيث تكون أعلى قيمة في الأعلى وتكون مرتبة 1. تعطي RANK الأرقام المتكررة نفس الترتيب ، ولكن يتم حسابها بشكل تراكمي. هذا يعني أنه إذا كانت هناك قيمتان في المرتبة 4 ، فإن الترتيب التالي سيكون 6 (1،2،3،4،4،6) بدلاً من 5.

=RANK(number, ref, [order])

لاحظ أنه يمكن ضبط [الترتيب] على 0 للترتيب التنازلي و 1 (أو أعلى) للترتيب التصاعدي.

نظرًا لأننا نريد ترتيب دخل العميل (العمود E) ، فإننا نستخدم الصيغة التالية في الخلية AH2:

= RANK (E2، E2: E2241، 0)

انقر فوق المربع الموجود في الركن الأيمن السفلي من الخلية AH2 واسحبه إلى آخر صف من البيانات لنسخ الصيغة تلقائيًا والسماح لك بإنشاء تحليل سريع لكل قيمة إدخال.

6- ميزة COUNTBLANK في الإكسل

تعد وظيفة COUNTBLANK بديهية إلى حد ما ، ولكنها وظيفة مهمة في جدل البيانات في التحليلات ، حيث إن العديد من خوارزميات التعلم الآلي حساسة للقيم الصفرية.

حيث ستمنحك معرفة عدد القيم الفارغة لديك فكرة أفضل عن كيفية التعامل معها.

على سبيل المثال ، يجب إزالة الأعمدة إذا كانت العديد من القيم فارغة. إذا كانت بعض القيم خالية ، فيجب أن تكون القيمة التي تملأ القيم الخالية سمة. تحسب COUNTBLANK عدد الخلايا الفارغة في النطاق.

الطريقة بسيطة:

=COUNTBLANK(range)

7- ميزة xlookup

يشبه XLOOKUP مجموعة من VLOOKUP و HLOOKUP ويسمح لك بالتمرير رأسيًا أو أفقيًا للبحث عن القيم في نطاق.

بشكل أساسي ، يمكنك استخدام النطاق المحدد كجدول بحث وإرجاع نتائج “البحث” في الخلية. الصيغة هي:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

لنفترض أننا نريد العثور على Year_Birth بناءً على قيمة المعرف التي تم إدخالها. في الخلية AD2 ، أدخل قيمة المعرف (على سبيل المثال 8755). أدخل صيغة XLOOKUP في الخلية AE2.

• = XLOOKUP (
• أدخل AD2 لأن lookup_value هي القيمة التي تبحث عنها الوظيفة.
• لاحظ أن lookup_array عبارة عن عمود أو صف يحتوي على قيم بحث. اكتب A2: A2241 وستحصل على مجموعة من المعرفات.
• return_array هو العمود أو الصف الذي يحتوي على القيم التي تريد إرجاعها. اختيار B2: سيعطيك B2241 قيمة Year_Birth.

يبدو التعبير المكتمل كما يلي:

= XLOOKUP (AD2، A2: A2241، B2: B2241)

سيؤدي إدخال معرّف مختلف إلى إرجاع Year_Birth المقابل.

وظيفة البحث قوية للغاية ، يمكنك دمج البيانات من أوراق مختلفة وحتى وظائف البحث المتداخل داخل بعضها البعض. على سبيل المثال ، يمكنك جمع قيم عمليات البحث المتعددة.

في هذه المقالة ، رأيت بعض الطرق لاستخدام الإكسل لتحليل بياناتك بطريقة أكثر احترافًا وأقصر.